/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package aptech.eproject;

import com.sun.rowset.CachedRowSetImpl;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;


/**
 *
 * @author Loveless
 */
public class DBConnection {

    private static String SERVER = "CHANGEME1\\SQL2005";
    private static String PORT = "1166";
private static String DB_NAME = "CanteenManagementSystem";
    private static String USER = "sa";
    private static String PASS = "1234567";
    private static Connection conn;
    public void setDB_NAME(String DB_NAME) {
        this.DB_NAME = DB_NAME;
    }

    public static void setPASS(String PASS) {
        DBConnection.PASS = PASS;
    }

    public static void setPORT(String PORT) {
        DBConnection.PORT = PORT;
    }

    public static void setSERVER(String SERVER) {
        DBConnection.SERVER = SERVER;
    }

    public static void setUSER(String USER) {
        DBConnection.USER = USER;
    }

    public DBConnection() {
    }
    
    public static Connection getConnect() throws Exception {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String url = "jdbc:sqlserver://"+SERVER+":"+PORT+";databaseName="+DB_NAME+";";
        return DriverManager.getConnection(url, USER, PASS);
    }
    public static ResultSet executeSQL(String sql) throws Exception{
        conn =getConnect();
        ResultSet resultset;
        Statement statement = conn.createStatement();
        resultset = statement.executeQuery(sql);
        return resultset;
    }
    public static CachedRowSetImpl executeCacheRowset(String sql) throws Exception{
            conn = getConnect();
            CachedRowSetImpl cachedRowSetImpl = new CachedRowSetImpl();
            cachedRowSetImpl.setUsername(USER);
            cachedRowSetImpl.setPassword(PASS);
            cachedRowSetImpl.setUrl("jdbc:sqlserver://"+SERVER+":"+PORT+";databaseName="+DB_NAME+";");
            int[] keys = {1};
            cachedRowSetImpl.setKeyColumns(keys);
            cachedRowSetImpl.execute(conn);
            conn.close();
            return cachedRowSetImpl;
            
    }
    public static void disposeConnect(){
        try {
            conn.close();
        } catch (SQLException ex) {
        }
    }
}
